﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using Models;
using Common;
using System.Xml.Linq;

namespace DbControl
{
    public class OprSql
    {
        public static string connectString;  
        #region DeviceInfo
        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="vo"></param>
        /// 数据对象vo
        /// <returns></returns>
        public static int Insert(DeviceInfo vo)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"INSERT INTO DeviceInfo
           (DeviceId 
           ,DeviceName 
           ,DeviceMac 
           ,CreateTime 
           ,Fe1 
           ,Fe2 
           ,Fe3 
           ,Fe4 
           ,Fe5 
           ,Fe6 
           ,Fe7 
           ,Fe8 
           ,Fe9 
           ,Fe10 
           ,NFe1 
           ,NFe2 
           ,NFe3 
           ,NFe4 
           ,NFe5 
           ,MFe1 
           ,MFe2 
           ,MFe3 
           ,MFe4 
           ,MFe5 
           ,MFe6 
           ,MFe7 
           ,MFe8 
           ,MFe9 
           ,MFe10 
           ,Fe 
           ,NFe 
           ,MFe 
           ,Dens 
           ,Vis 
           ,Vis40 
           ,Temp 
           ,Dc 
           ,Vol 
           ,Ppm 
           ,Aw 
           ,Ch4 
           ,Ch6 
           ,Ch14 
           ,Ch21 
           ,Isoch4 
           ,Isoch6 
           ,Isoch14 
           ,Isoch21 
           ,Nas)
     VALUES
           (@DeviceId  
           ,@DeviceName 
           ,@DeviceMac 
           ,@CreateTime 
           ,@Fe1  
           ,@Fe2  
           ,@Fe3  
           ,@Fe4  
           ,@Fe5  
           ,@Fe6  
           ,@Fe7  
           ,@Fe8  
           ,@Fe9  
           ,@Fe10  
           ,@NFe1  
           ,@NFe2  
           ,@NFe3  
           ,@NFe4  
           ,@NFe5  
           ,@MFe1  
           ,@MFe2  
           ,@MFe3  
           ,@MFe4  
           ,@MFe5  
           ,@MFe6  
           ,@MFe7  
           ,@MFe8  
           ,@MFe9  
           ,@MFe10  
           ,@Fe  
           ,@NFe  
           ,@MFe  
           ,@Dens
           ,@Vis 
           ,@Vis40
           ,@Temp  
           ,@Dc
           ,@Vol
           ,@Ppm  
           ,@Aw 
           ,@Ch4  
           ,@Ch6  
           ,@Ch14  
           ,@Ch21  
           ,@Isoch4  
           ,@Isoch6  
           ,@Isoch14  
           ,@Isoch21  
           ,@Nas)";
                    return connection.Execute(sql, vo);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
       /// <summary>
       /// 加载曲线数据
       /// </summary>
       /// <param name="deviceId"></param>
       /// 设备识别码
       /// <param name="startT"></param>
       /// 开始时间
       /// <param name="endT"></param>
       /// 结束时间
       /// <returns></returns>
       /// 数据表格
        public static DataTable LoadCurveByDeviceId(string deviceId,string startT,string endT)  
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string sql = @"SELECT top 60 
                  CreateTime                 
                  ,Temp 
                  ,Vis  
                  ,Vis40 
                  ,Ppm 
                  ,dens                   
                  ,dc                  
                  ,Fe1 
                  ,Fe2 
                  ,Fe3 
                  ,Fe4 
                  ,Fe5 
                  ,Fe6 
                  ,Fe7 
                  ,Fe8 
                  ,Fe9 
                  ,Fe10 
                  ,NFe1 
                  ,NFe2 
                  ,NFe3 
                  ,NFe4                   
              FROM DeviceInfo where  CreateTime between '{0}' and '{1}'and DeviceId='{2}'  order by Id desc";
                sql = string.Format(sql, startT, endT, deviceId);
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        public static DeviceInfo GetDeviceInfoByDeviceId(string deviceId)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string sql = @"SELECT top 1  Id 
                              ,DeviceId 
                              ,DeviceName 
                              ,DeviceMac 
                              ,CreateTime 
                              ,Fe1 
                              ,Fe2 
                              ,Fe3 
                              ,Fe4 
                              ,Fe5 
                              ,Fe6 
                              ,Fe7 
                              ,Fe8 
                              ,Fe9 
                              ,Fe10 
                              ,MFe1 
                              ,MFe2 
                              ,MFe3 
                              ,MFe4 
                              ,MFe5 
                              ,MFe6 
                              ,MFe7 
                              ,MFe8 
                              ,MFe9 
                              ,MFe10 
                              ,NFe1 
                              ,NFe2 
                              ,NFe3 
                              ,NFe4 
                              ,NFe5 
                              ,Fe 
                              ,NFe 
                              ,MFe 
                              ,Dens 
                              ,Vis 
                              ,Vis40
                              ,Temp 
                              ,Dc 
                              ,Vol 
                              ,Ppm 
                              ,Aw 
                              ,Ch4 
                              ,Ch6 
                              ,Ch14 
                              ,Ch21 
                              ,Isoch4 
                              ,Isoch6 
                              ,Isoch14 
                              ,Isoch21 
                              ,Nas 
                          FROM DeviceInfo  Where DeviceId=@DeviceId order by Id desc ";
                return connection.Query<DeviceInfo>(sql, new { DeviceId = deviceId }).SingleOrDefault();
            }
        }
        public static DataTable GetDtFromChiLun(DateTime d1, DateTime d2, string Name)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string sql = @"SELECT Id
                          ,DeviceId
                          ,DeviceName
                          ,DeviceMac
                          ,CreateTime
                          ,Temp
                          ,Dens
                          ,Vis
                          ,Vis40
                          ,Dc
                          ,Vol
                          ,Ppm
                          ,Fe1
                          ,Fe2
                          ,Fe3
                          ,Fe4
                          ,Fe5
                          ,Fe6
                          ,Fe7
                          ,Fe8
                          ,Fe9
                          ,Fe10
                          ,NFe1
                          ,NFe2
                          ,NFe3
                          ,NFe4
                          ,NFe5
                          ,MFe1
                          ,MFe2
                          ,MFe3
                          ,MFe4
                          ,MFe5
                          ,MFe6
                          ,MFe7
                          ,MFe8
                          ,MFe9
                          ,MFe10
                          ,Fe
                          ,NFe
                          ,MFe                         
                          ,Aw                       
                      FROM DeviceInfo WHERE CreateTime between @t1 and @t2 order by Id asc";
                if (!string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT Id
                          ,DeviceId
                          ,DeviceName
                          ,DeviceMac
                          ,CreateTime
                          ,Temp
                          ,Dens
                          ,Vis
                          ,Vis40
                          ,Dc
                          ,Vol
                          ,Ppm
                          ,Fe1
                          ,Fe2
                          ,Fe3
                          ,Fe4
                          ,Fe5
                          ,Fe6
                          ,Fe7
                          ,Fe8
                          ,Fe9
                          ,Fe10
                          ,NFe1
                          ,NFe2
                          ,NFe3
                          ,NFe4
                          ,NFe5
                          ,MFe1
                          ,MFe2
                          ,MFe3
                          ,MFe4
                          ,MFe5
                          ,MFe6
                          ,MFe7
                          ,MFe8
                          ,MFe9
                          ,MFe10
                          ,Fe
                          ,NFe
                          ,MFe                         
                          ,Aw                       
                      FROM DeviceInfo WHERE CreateTime between @t1 and @t2  and DeviceName=@DeviceName order by Id asc";
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql, new { t1 = d1, t2 = d2, DeviceName=Name }));
                return dt;
            }
        }
        public static DataTable GetDtFromYeYa(DateTime d1, DateTime d2, string Name)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string sql = @"SELECT Id
                          ,DeviceId
                          ,DeviceName
                          ,DeviceMac
                          ,CreateTime
                          ,Dens
                          ,Vis
                          ,Temp
                          ,Dc
                          ,Vol
                          ,Ppm
                          ,Aw
                          ,Ch4
                          ,Ch6
                          ,Ch14
                          ,Ch21
                          ,Isoch4
                          ,Isoch6
                          ,Isoch14
                          ,Isoch21
                          ,Nas
                      FROM DeviceInfo WHERE CreateTime between @t1 and @t2 order by Id asc";
                if (!string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT Id
                          ,DeviceId
                          ,DeviceName
                          ,DeviceMac
                          ,CreateTime                       
                          ,Dens
                          ,Vis
                          ,Temp
                          ,Dc
                          ,Vol
                          ,Ppm
                          ,Aw
                          ,Ch4
                          ,Ch6
                          ,Ch14
                          ,Ch21
                          ,Isoch4
                          ,Isoch6
                          ,Isoch14
                          ,Isoch21
                          ,Nas
                      FROM DeviceInfo WHERE CreateTime between @t1 and @t2  and DeviceName=@DeviceName order by Id asc";
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql, new { t1 = d1, t2 = d2, DeviceName = Name }));
                return dt;
            }
        }
        #endregion
        #region Device
        public static List<Device> GetDeviceAll()
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"SELECT  Id 
                              , DeviceId 
                              , DeviceName 
                              , DeviceSite 
                              , DeviceOil 
                              , XishuA 
                              , XishuB 
                              , topic 
                              ,Flag
                          FROM   Device WHERE Flag>0 ";
                    return connection.Query<Device>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return null;
            }
        }
        public static DataTable GetDeviceByName(string Name)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string sql = "";
                if (string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT Id 
                            , DeviceId 
                            , DeviceName 
                            , DeviceSite 
                            , DeviceOil 
                            , XishuA 
                            , XishuB 
                            , topic 
                            ,Flag
                        FROM Device";
                }
                else
                {
                    sql =string.Format(@"SELECT Id 
                          , DeviceId 
                          , DeviceName 
                          , DeviceSite 
                          , DeviceOil 
                          , XishuA 
                          , XishuB 
                          , topic 
                          ,Flag
                      FROM Device WHERE   DeviceName='{0}'", Name);
                }               
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        public static Device GetDeviceById(long Id)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string sql = @"SELECT Id 
                            , DeviceId 
                            , DeviceName 
                            , DeviceSite 
                            , DeviceOil 
                            , XishuA 
                            , XishuB 
                            , topic 
                            ,Flag
                        FROM Device Where Id=@Id ";
                return connection.Query<Device>(sql,new { Id=Id}).SingleOrDefault();                
            }
        }
        public static Device GetDeviceByDeviceId(string deviceId)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string sql = @"SELECT Id 
                            , DeviceId 
                            , DeviceName 
                            , DeviceSite 
                            , DeviceOil 
                            , XishuA 
                            , XishuB 
                            , topic 
                        FROM Device Where DeviceId=@DeviceId ";
                return connection.Query<Device>(sql, new { DeviceId = deviceId }).SingleOrDefault();
            }
        }
        public static bool IsExistDeviceId(string dv)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"select COUNT(Id)  from Device WHERE  DeviceId =@DeviceId ";
                    intNum=(int)connection.ExecuteScalar(sql, new { DeviceId = dv });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());               
            }            
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static bool IsExistDeviceName(string name)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"select COUNT(Id)  from Device WHERE  DeviceName =@DeviceName ";
                    intNum = (int)connection.ExecuteScalar(sql, new { DeviceName = name });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static bool IsExistDevicetopic(string topic)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"select COUNT(Id)  from Device WHERE  topic =@topic ";
                    intNum = (int)connection.ExecuteScalar(sql, new { topic = topic });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static int DeviceDel(int Id)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"DELETE FROM Device WHERE Id=@Id";
                    sql= @"UPDATE Device SET Flag = 0 WHERE Id=@Id";
                    return connection.Execute(sql, new { Id = Id });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int DeviceUpdate(Device dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"UPDATE Device 
                            SET  DeviceId  =  @DeviceId 
                                , DeviceName  =  @DeviceName 
                                , DeviceSite  =  @DeviceSite 
                                , DeviceOil  =  @DeviceOil 
                                , XishuA  =  @XishuA
                                , XishuB  =  @XishuB
                                , topic  =  @topic
                                , Flag=@Flag
                            WHERE Id=@Id";
                    return connection.Execute(sql,dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int DeviceInsert(Device dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"INSERT INTO Device
                               ( DeviceId 
                               , DeviceName 
                               , DeviceSite 
                               , DeviceOil 
                               , XishuA 
                               , XishuB 
                               , topic
                               , Flag)
                         VALUES
                               ( @DeviceId
                               , @DeviceName
                               , @DeviceSite
                               , @DeviceOil 
                               , @XishuA  
                               , @XishuB
                               , @topic 
                               , @Flag)";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        #endregion
        #region OilPara
        public static List<string> GetID(string IDS)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"select ID  from Product WHERE  ID =@ID ";
                    return connection.Query<string>(sql, new { ID = IDS }).ToList();
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                List<string> list = new List<string>();
                return list;
            }
        }
        public static List<OilPara> GetOilParaAll()
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"SELECT  Id 
                              , Mc 
                              , Dw 
                              , RoundStr 
                              , Flag 
                          FROM   OilPara  ";
                    return connection.Query<OilPara>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return null;
            }
        }
        #endregion
        #region DevicePara
        public static DataTable GetDeviceParaByWhere(string Name,string Para)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string strFilter = " WHERE  DeviceId is not null";
                if (!string.IsNullOrEmpty(Name))
                {
                    strFilter += string.Format(" AND DeviceName='{0}'",Name);
                }
                if (!string.IsNullOrEmpty(Para))
                {
                    strFilter += string.Format(" AND ParaName='{0}'", Para);
                }
                string sql =string.Format(@"SELECT  Id 
                              , DeviceId 
                              , DeviceName 
                              , ParaId 
                              , ParaName 
                              , UpperVal 
                              , LowerVal 
                              , Flag
                          FROM  DevicePara {0} ", strFilter);                
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        public static bool IsExistDeviceParaBydvp(string dv,string para)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"select COUNT(Id)  from DevicePara WHERE  DeviceName =@DeviceName and ParaName=@ParaName";
                    intNum = (int)connection.ExecuteScalar(sql, new { DeviceName = dv,ParaName=para });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }
        public static int DeviceParaInsert(DevicePara dp)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"INSERT INTO DevicePara
                                (DeviceId
                                , DeviceName
                                , ParaId
                                , ParaName
                                , UpperVal
                                , LowerVal
                                , Flag)
                            VALUES
                                (@DeviceId
                                , @DeviceName
                                , @ParaId
                                , @ParaName
                                , @UpperVal
                                , @LowerVal
                                , @Flag)";
                    return connection.Execute(sql, dp);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }           
        }
        public static int DeviceParaUpdate(DevicePara dp)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"UPDATE DevicePara
                                    SET  DeviceId  = @DeviceId
                                    , DeviceName  = @DeviceName
                                    , ParaId  = @ParaId
                                    , ParaName  = @ParaName
                                    , UpperVal  = @UpperVal
                                    , LowerVal  = @LowerVal
                                    , Flag  = @Flag
                                    WHERE  Id=@Id";
                    return connection.Execute(sql, dp);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int DeviceParaDel(DevicePara dp)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"DELETE FROM DevicePara WHERE Id=@Id";

                    return connection.Execute(sql,dp);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int DeviceParaDel(long Id)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"DELETE FROM DevicePara WHERE Id=@Id";

                    return connection.Execute(sql, new { Id = Id });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static DevicePara GetDeviceParaById(long Id)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string sql = @"SELECT  Id 
                              , DeviceId 
                              , DeviceName 
                              , ParaId 
                              , ParaName 
                              , UpperVal 
                              , LowerVal 
                              , Flag 
                          FROM  DevicePara  Where Id=@Id ";
                return connection.Query<DevicePara>(sql, new { Id = Id }).SingleOrDefault();
            }
        }
        /// <summary>
        /// 初始化获取上下限
        /// </summary>
        /// <param name="dvId"></param>
        /// <returns></returns>
        public static List<DevicePara> GetDeviceParaByDeviceId(string dvId)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                string sql = @"SELECT  Id 
                              , DeviceId 
                              , DeviceName 
                              , ParaId 
                              , ParaName 
                              , UpperVal 
                              , LowerVal 
                              , Flag 
                          FROM  DevicePara  Where DeviceId=@DeviceId ";
                return connection.Query<DevicePara>(sql, new { DeviceId = dvId }).ToList();
            }
        }
        #endregion
        public static int CountWeiXin()
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(connectString))
                {
                    string sql = @"select COUNT(ID)  from  WeiXin ";
                    return (int)connection.ExecuteScalar(sql);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }

        public static DataTable SelectBySql(string sql)
        {
            using (IDbConnection connection = new SqlConnection(connectString))
            {
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }

        }
    }
}
